package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.AdSeller;
import cn.com.dashihui.web.dao.GSR;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.StoreImages;
import cn.com.dashihui.web.dao.User;

public class StoreService {
	/**
	 * 分页查找
	 */
	public Page<Record> findByPage(int pageNum, int pageSize, String sellerId, String beginDate,String endDate,int state,String sId){
		StringBuffer sBuffer = new StringBuffer(" FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId inner join  t_auth_admin_seller B on A.userid=B.id WHERE  1=1");
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId) && !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(sId)){
			sBuffer.append(" AND T.storeid=?");
			params.add(sId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.starttime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		sBuffer.append(" ORDER BY T.starttime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.createtime,T.starttime,T.aname,T.aphone,T.checktime,T.state", sBuffer.toString(), params.toArray());
	}
	
	public boolean addStore(Store newObject){
		return newObject.save();
	}
	
	public boolean delStore(int id){
		return Store.me().deleteById(id);
	}
	
	public boolean editStore(Store object){
		return object.update();
	}

	public Record findById(String storeid) {
		return Db.findFirst("select storeid,title,aname,aphone,qstate from t_dict_store where storeid=?",storeid);
	}
	public List<Record> findAllSeller() {
		return Db.find("select T.sellerId,T.sellerName,T.userid from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id");
	}
	/**
	 * 查询所有分公司
	 */
	public List<Record> findAllStore() {
		return Db.find("select T.storeid,T.title,T.sellerId from t_dict_store T inner JOIN t_auth_admin_seller A ON T.storeid = A.storeid");
	}
	public List<Record> chooseStore(String sSellerId) {
		return Db.find("select id,storeid,title from t_dict_store where sellerId in ('"+sSellerId+"')");
	}
	public List<Record> chooseAllStore() {
		return Db.find("select * from t_dict_store");
	}
	public int doCancel(String reason,String storeid, String checktime) {
		return Db.update("update t_dict_store set state=4 ,checktime=?,reason=? where storeid=?",checktime,reason,storeid);
	}
	public int doAccept(String storeid, String checktime) {
		return Db.update("update t_dict_store set dixian=predixian,point=prepoint, state=2 ,checktime=? where storeid=?",checktime,storeid);
	}
	public Store findByStoreid(String storeid) {
		return Store.me().findFirst("SELECT T.id,T.storeid,T.title,T.scope,A.sellerName,T.createtime,T.starttime,T.aname,T.aphone,T.checktime,T.state "
				+ " FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId  WHERE T.storeid=?",storeid);
	}
	public Record detail(String storeid) {
		return Db.findFirst("select * from t_dict_store where storeid=?",storeid);
	}
	/**
	 * 分公司图片列表
	 * @param storeid
	 * @return
	 */
	public List<StoreImages> findAllImages(String stroeimagenum){
		return StoreImages.me().find("SELECT A.* FROM t_bus_store_images A WHERE A.stroeimagenum=? ORDER BY A.orderNo",stroeimagenum);
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "分公司上线列表", titles, listz);
	}
	public List<Record> findExcelByPage(String sellerId, String sId,String beginDate, String endDate, int state) {
		StringBuffer sBuffer = new StringBuffer("SELECT T.storeid,T.title,T.scope,A.sellerName,T.createtime,T.starttime,T.aname,T.aphone,T.checktime,T.state FROM t_dict_store T inner join t_bus_seller A on T.sellerId=A.sellerId inner join  t_auth_admin_seller B on A.userid=B.id WHERE T.state !=3 and 1=1");
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId) && !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(sId)){
			sBuffer.append(" AND T.storeid=?");
			params.add(sId);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.starttime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.starttime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notNull(state)&&state!=0){
			sBuffer.append(" AND T.state=?");
			params.add(state);
		}
		sBuffer.append(" ORDER BY T.starttime DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}	
	/**
	 * 根据id查询当前通过审核的store
	 * @param storeid
	 * @return
	 */
	public Store findStore(String storeid) {
		return Store.me().findFirst("SELECT * FROM t_dict_store T WHERE T.storeid=?",storeid);
	}
	/**
	 * 根据电话号码获取用户对象
	 * @param phone
	 * @return
	 */
	public User getUserByPhone(String phone){
		return User.me().findFirst("SELECT name from t_bus_user where phone=?", phone);
	}
	/**
	 * 根据分公司所属的公司sellerid获取主键id
	 * @param phone
	 * @return
	 */
	public Seller findSeller(String sellerId){
		return Seller.me().findFirst("SELECT * from t_bus_seller where sellerid=?", sellerId);
	}
	/**
	 * 根据分公司所属的公司sellerid获取主键id
	 * @param phone
	 * @return
	 */
	public AdSeller findAdSeller(int userid){
		return AdSeller.me().findFirst("SELECT * from t_bus_admin_seller where userid=?", userid);
	}
	/**
	 * 查询对应的商品关系
	 * @param storeid
	 * @return
	 */
	public List<GSR> finds(String storeid) {
		return GSR.me().find("select * from t_bus_goods_store_rel where storeid=?",storeid);
	}
	/**
	 * 下架所有商品
	 */
	public int downGoods(String goodstore){
		return Db.update("update t_bus_goods set state=3 where good_store=?", goodstore);
	}
	/**
	 * 下线所有分公司
	 */
	public int downStore(String downtime,String storeid){
		return Db.update("update t_dict_store set state=3,downtime=? where storeid=?", downtime,storeid);
	}
	/**
	 * 新添加的用户是分公司
	 */
	public int addRolesDj(int userid){
		return Db.update("INSERT INTO t_auth_admin_roles_seller(userid,roleid) VALUES( ?,'3')", userid);
	}
}